/**
 * DaoPtz.java
 *2013年10月7日下午7:38:25
 *LINKT
 */
package com.jskj.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.jskj.commons.DBConn;

/**
 * @author LINKT
 *
 */
public class DaoPtz {

        public String getPtzList(int page ,int limit,String sbyf,String jyz_bh) {
                String sql="";
                DBConn conn=new DBConn();
                int rowEnd=page*limit;
                String str="";
                sql="select c.jyz_mc,c.jyz_sh,b.* from "+
                                "(select a.jyz_bh , "+
                                        "sum(case when [yp]='102' then [ylsum] else 0 end)as yp_93, "+
                                        "sum(case when [yp]='104' then [ylsum] else 0 end)as yp_97, "+
                                        "sum(case when [yp]='202' then [ylsum] else 0 end)as yp_0, "+
                                        "sum(case when [yp]not in('102','104','202') then [ylsum] else 0 end)as yp_other, "+
                                        "sum(ylsum) as yp_sum, "+
                                        "'"+sbyf+"' as sbyf "+
                                "from v_yzsum a "+
                                "where a.ny='"+sbyf+"' "+
                                "group by a.jyz_bh) b "+
                        "join jyz c on (b.jyz_bh=c.jyz_bh) "+
                        "where c.jyz_bh='"+jyz_bh+"' or c.jyz_bh in(select jyz_ptz from jyzgx where jyz_zxz='"+jyz_bh+"') ";
                int totalcount=conn.SelectSQLCount(sql);
                StringBuffer sb=new StringBuffer();
                String sSQL="select * from ("+
                                "    select top "+limit+" * from("+
                                "   select top "+rowEnd+" * "+
                                "    from ("+sql+") as aa order by jyz_bh) as bb order by jyz_bh desc ) as cc order by jyz_bh ";
                ResultSet rs=conn.SelectSQL(sSQL);
                try {
                        while (rs.next()) {
                                sb.append("{\"jyz_mc\":\""+rs.getString("jyz_mc")+"\",\"jyz_sh\":\""+rs.getString("jyz_sh")+"\",\"sbyf\":\""+rs.getString("sbyf")+"\","
                                                + "\"yp_93\":\""+rs.getString("yp_93")+"\","
                                                + "\"yp_97\":\""+rs.getString("yp_97")+"\","
                                                + "\"yp_0\":\""+rs.getString("yp_0")+"\","
                                                + "\"yp_other\":\""+rs.getString("yp_other")+"\","
                                                + "\"yp_sum\":\""+rs.getString("yp_sum")+"\""
                                                                + "},");
                        }
                        rs.close();
                        conn.Close();
                        if (sb.length()>0) {
                                sb=sb.deleteCharAt(sb.length()-1);
                        }
                        str="{\"totalCount\":"+totalcount+",\"items\":["+sb.toString()+"]}";;
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                
                return str;
        }
        public String getPtzMxList(int page ,int limit,String sbyf,String jyz_bh){
                String sql="";
                DBConn conn=new DBConn();
                int rowEnd=page*limit;
                String str="";
                sql="select * from v_yzmx where ny='"+sbyf+"' and jyz_bh='"+jyz_bh+"'";
                int totalcount=conn.SelectSQLCount(sql);
                StringBuffer sb=new StringBuffer();
                String sSQL="select * from ("+
                                "    select top "+limit+" * from("+
                                "   select top "+rowEnd+" * "+
                                "    from ("+sql+") as aa order by jyz_bh) as bb order by jyz_bh desc ) as cc order by jyz_bh ";
                ResultSet rs=conn.SelectSQL(sSQL);
                try {
                        while (rs.next()) {
                                sb.append("{\"jyz_mc\":\""+rs.getString("jyz_mc")+"\",\"jyz_sh\":\""+rs.getString("jyz_sh")+"\",\"sbyf\":\""+rs.getString("ny")+"\","
                                                + "\"yqbh\":\""+rs.getString("yqbh")+"\","
                                                + "\"jjxh\":\""+rs.getString("jjxh")+"\","
                                                + "\"ypxh_mc\":\""+rs.getString("ypxh_mc")+"\","
                                                + "\"yl\":\""+rs.getString("yl")+"\","
                                                + "\"je\":\""+rs.getString("je")+"\""
                                                                + "},");
                        }
                        rs.close();
                        conn.Close();
                        if (sb.length()>0) {
                                sb=sb.deleteCharAt(sb.length()-1);
                        }
                        str="{\"totalCount\":"+totalcount+",\"items\":["+sb.toString()+"]}";;
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                
                return str;
        }
}
